I had heard of Data Use Case Domains in 23ai. However, the New Features nd Database Concepts documentation didn't provide enough examples for me to build on.
However, this blog post by Ulrike Schwinn (which was shared by @thatjeffsmith on X) helped me explore domains.
In this demo, I am using the Pre-Seeded Domains. However, you can see the example posted by Ulrike Schwimm or even read in the Database Concepts documentation to help build your own custom Domains.
A Data Use Case Domain is like defining a Custom DataType such that only valid values are permitted. The Domain name can be a self-identifier (just as "DATE" or "NUMBER" identifies the type of data being stored).
Here is my demonstration (I also use the Annotations feature -- the Data Use Case Domains documentation links above also lead to this feature)
SQL> set pages600 linesize 132
SQL> col contact_person format a32
SQL> col contact_email format a24
SQL>
SQL> drop table forex_rates_contacts;
Table dropped.
SQL>
SQL>
SQL> create table forex_rates_contacts
2 (
3 country_iso_code varchar2(3) domain country_code_d, -- preseeded SYS domain
4 currency_code varchar2(3) domain currency_code_d, -- preseeded SYS domain
5 contact_person varchar2(128),
6 contact_email varchar2(4000) domain email_d -- preseed SYS domain
7 )
8 annotations (display 'Forex Contact Persons')
9 /
Table created.
SQL>
SQL> desc forex_rates_contacts
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
COUNTRY_ISO_CODE VARCHAR2(3) SYS.COUNTRY_CODE_D
CURRENCY_CODE VARCHAR2(3) SYS.CURRENCY_CODE_D
CONTACT_PERSON VARCHAR2(128)
CONTACT_EMAIL VARCHAR2(4000) SYS.EMAIL_D
SQL>
SQL>
SQL> set long 1000
SQL> set longc 1000
SQL> set serveroutput on
SQL>
SQL> rem FROM clause is no longer required in 23ai
SQL> select dbms_metadata.get_ddl('TABLE','FOREX_RATES_CONTACTS','HEMANT');
DBMS_METADATA.GET_DDL('TABLE','FOREX_RATES_CONTACTS','HEMANT')
------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "HEMANT"."FOREX_RATES_CONTACTS"
( "COUNTRY_ISO_CODE" VARCHAR2(3) DOMAIN "SYS"."COUNTRY_CODE_D",
"CURRENCY_CODE" VARCHAR2(3) DOMAIN "SYS"."CURRENCY_CODE_D",
"CONTACT_PERSON" VARCHAR2(128),
"CONTACT_EMAIL" VARCHAR2(4000) DOMAIN "SYS"."EMAIL_D"
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
ANNOTATIONS("DISPLAY" 'Forex Contact Persons')
SQL>
SQL>
SQL>
SQL> rem MULTI-ROW Insert
SQL> insert into forex_rates_contacts
2 values
3 ('US','USD','Mr Unknown','unknown@nowhere.gov'),
4 ('IN','INR','Someone at RBI','someone@rbi.gov.in')
5 /
2 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from forex_rates_contacts
2 order by country_iso_code
3 /
COU CUR CONTACT_PERSON CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN INR Someone at RBI someone@rbi.gov.in
US USD Mr Unknown unknown@nowhere.gov
SQL>
SQL> -- Note that the country_code_d and currency_code_d do not check validity against really ISO codes
SQL> -- thus, it does not disallow "ZZ" and "ZZZ"
SQL> insert into forex_rates_contacts
2 values
3 ('ZZ','ZZZ','Mr Unknown','unknown@nowhere.zz')
4 /
1 row created.
SQL>
SQL> select * from forex_rates_contacts
2 order by country_iso_code
3 /
COU CUR CONTACT_PERSON CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN INR Someone at RBI someone@rbi.gov.in
US USD Mr Unknown unknown@nowhere.gov
ZZ ZZZ Mr Unknown unknown@nowhere.zz
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- But the rules for email validation are encoded
SQL> insert into forex_rates_contacts
2 values
3 ('UK','GBP','Mr Someone','someone@x')
4 /
insert into forex_rates_contacts
*
ERROR at line 1:
ORA-11534: check constraint (HEMANT.SYS_C0013464) involving column CONTACT_EMAIL due to domain constraint SYS.SYS_DOMAIN_C0030 of
domain SYS.EMAIL_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL>
SQL> select * from forex_rates_contacts
2 order by country_iso_code
3 /
COU CUR CONTACT_PERSON CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN INR Someone at RBI someone@rbi.gov.in
US USD Mr Unknown unknown@nowhere.gov
ZZ ZZZ Mr Unknown unknown@nowhere.zz
SQL>
SQL> spool off
I haven't added my own custom Domains but used the PreSeeded domains for Country, Currency and Email. Look at "10.1.12 Built-In Use Case Domains" in
the documentation.